package net.comm.blog.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class BlogServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Blog where  blogID=? and follID=? and UpFollID=? ";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Blog ( blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg,  blogSet, status,  mtime )" +
				" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Blog  set title=?, intro=?,  content=?,  tags=?,  f_ID=?," +
				" pubTime=?,  userID=?, upUserID=?, attachNum=?, replyNum=?,  firsort=?,  secsort=?,  thisort=?," +
				" replyFlg=?, blogSet=?, status=?,  mtime=? " +
				" where  blogID=? and follID=? and UpFollID=? ";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdAttRepSql()
	{
		return "update Blog  set attachNum=?, replyNum=?, blogSet=?,  status=? " +
				" where  blogID=? and follID=? and UpFollID=? ";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select  blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg,  blogSet, status,  mtime " +
				" from Blog where  blogID=? and follID=? and UpFollID=?  ";
	}
	
	/**
	 * 返回根据主键查询语句
	 */
	public String getbyblogIDSql()
	{
		return "select  blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg,  blogSet, status,  mtime " +
				" from Blog where  blogID=?  ";
	}

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
		sb.append(" where userID=? and follID = 0  and  upFollID = 0  Order by blogID desc, follID  ");
		sb.append(" limit ?,? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getcommentbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
		sb.append(" where 1=1 and follID not in ( 0 ) and  upFollID not in ( 0 ) and userID=? " +
				" Order by blogID, follID  ");
		sb.append(" limit ?,? ");
				
		return sb.toString();
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getblogcommentbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
		sb.append(" where blogID=? and follID not in ( 0 )  " +
				" Order by follID, upFollID  ");
		sb.append(" limit ?,? ");
				
		return sb.toString();
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
				
		return sb.toString();	
	}
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getMaxfollIDRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
		sb.append(" where blogID=? Order by follID desc ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getMaxUpfollIDRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
		sb.append(" where blogID=? and follID=? Order by upFollID desc ");
				
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getMaxupfollIDRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select blogID,  follID, upFollID, title, intro,  content,  tags,  f_ID," +
				" pubTime,  userID, upUserID, attachNum, replyNum,  firsort,  secsort,  thisort," +
				" replyFlg, blogSet,  status,  mtime ");
		sb.append(" from Blog ");
		sb.append(" where blogID=? and follID=? and UpFollID=? Order by follID desc ");
				
		return sb.toString();	
	}	
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Blog ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Blog where follID = 0 and " +
				" upFollID = 0 and UserID=? ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from Blog where follID not in ( 0 ) and blogID=? ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql4()
	{
		return "select count(*) as COUNT from Blog where follID not in ( 0 )  and " +
				" upFollID not in ( 0 ) and UserID=? ";
	}


	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
